package org.seler.spring.data.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.seler.spring.data.jdbc.pojo.WorkOrdInfoDO;
import org.seler.util.DateUtil;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

public class WorkOrderDaoJDBCImpl implements WorkOrderDao {

	public final static String RETRIEVE_WORKORDER_BY_WORKDERNO = "SELECT /*+FIRST_ROW*/ ACCT_ID, WORK_ORD_CREATE_DT, CANCELLED_CD, CANCELLED_DT, CANCELLED_REASON, CUST_REQ_DT, DEALER_CD, DISCONNECT_CD, DISCONNECT_REASON, JOB_EFFECTIVE_DT, LAST_UPDATE_DT, LAST_UPDATE_ID, ORD_PRIORITY, SALESMAN_CD, SVC_ACTION_TY, SVC_INST_ID, SVC_ORD_NO,WORK_ORD_COMPLETION_DT, WORK_ORD_CREATE_DT, WORK_ORD_CREATE_ID, WORK_ORD_NO, WORK_ORD_STATUS, WORK_ORD_TY  FROM OP_WORK_ORD  WHERE WORK_ORD_NO = ?";

	public final static String RETRIEVE_WORKORDER_ALL = "SELECT /*+ALL_ROWS*/ * FROM OP_WORK_ORD where rownum < ?";
	
	private JdbcTemplate jdbcTemplate;

	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}

	public WorkOrdInfoDO retrieveWorkOrderByWorkOrderNo(String workOrderNo) {

		return this.jdbcTemplate.queryForObject(
				RETRIEVE_WORKORDER_BY_WORKDERNO, new Object[] { workOrderNo },
				new WorkMapper());
	}	

	public List<WorkOrdInfoDO> retrievieWorkOrder(int recordNumber) {
		
		return this.jdbcTemplate.query(
				RETRIEVE_WORKORDER_ALL, new Object[] { recordNumber + 1 },	new WorkMapper());
				
	}
	
	private static final class WorkMapper implements RowMapper<WorkOrdInfoDO> {
		public WorkOrdInfoDO mapRow(ResultSet rs, int rowNum)
				throws SQLException {
			WorkOrdInfoDO workOrdInfoDO = new WorkOrdInfoDO();
			workOrdInfoDO.setAcctId(rs.getString("ACCT_ID"));
			workOrdInfoDO.setApplicationMode("Test_Spring");
			workOrdInfoDO.setBillEffDt(DateUtil.convertDateToString(rs.getDate("WORK_ORD_CREATE_DT")));
			workOrdInfoDO.setCancelledCd(rs.getString("CANCELLED_CD"));
			workOrdInfoDO.setCancelledDt(DateUtil.convertDateToString(rs.getDate("CANCELLED_DT")));
			workOrdInfoDO.setCancelledReason(rs.getString("CANCELLED_REASON"));
			workOrdInfoDO.setCustReqDt(DateUtil.convertDateToString(rs.getDate("CUST_REQ_DT")));
			workOrdInfoDO.setDealerCd(rs.getString("DEALER_CD"));
			workOrdInfoDO.setDisconnectCd(rs.getString("DISCONNECT_CD"));
			workOrdInfoDO
					.setDisconnectReason(rs.getString("DISCONNECT_REASON"));
			workOrdInfoDO.setJobEffectiveDt(DateUtil.convertDateToString(rs.getDate("JOB_EFFECTIVE_DT")));
			workOrdInfoDO.setLastUpdateDt(DateUtil.convertDateToString(rs.getDate("LAST_UPDATE_DT")));
			workOrdInfoDO.setLastUpdateId(rs.getString("LAST_UPDATE_ID"));
			workOrdInfoDO.setOrdPriority(rs.getString("ORD_PRIORITY"));
			workOrdInfoDO.setSalesmanCd(rs.getString("SALESMAN_CD"));
			workOrdInfoDO.setSvcActionType(rs.getString("SVC_ACTION_TY"));
			workOrdInfoDO.setSvcInstId(rs.getString("SVC_INST_ID"));
			workOrdInfoDO.setSvcOrdNo(rs.getString("SVC_ORD_NO"));
			workOrdInfoDO.setWorkOrdCompletionDt(DateUtil.convertDateToString(rs.getDate("WORK_ORD_COMPLETION_DT")));
			workOrdInfoDO
					.setWorkOrdCreateDt(DateUtil.convertDateToString(rs.getDate("WORK_ORD_CREATE_DT")));
			workOrdInfoDO
					.setWorkOrdCreateId(rs.getString("WORK_ORD_CREATE_ID"));
			workOrdInfoDO.setWorkOrdNo(rs.getString("WORK_ORD_NO"));
			workOrdInfoDO.setWorkOrdStatus(rs.getString("WORK_ORD_STATUS"));
			workOrdInfoDO.setWorkOrdType(rs.getString("WORK_ORD_TY"));
			return workOrdInfoDO;
		}
	}
	

}
